Write number of pills in terms of millions in seperate column:
import pandas as pd
import plotly.express as px
import sqlite3 as sq3
# Reading .db file and creating a few pandas dataframes from it
con = sq3.connect('opioid.db') # has annual, land, and population tables
annual = pd.read_sql_query('SELECT * from annual', con)
land = pd.read_sql_query('SELECT * from land', con)
pop = pd.read_sql_query('SELECT * from population', con)
con.close
<function Connection.close>
annual = annual.assign(Pills_in_millions = pd.to_numeric(annual.DOSAGE_UNIT)/1000000)
annual
| ? | BUYER_COUNTY | BUYER_STATE | year | count | DOSAGE_UNIT | countyfips | Pills_in_millions | |
|---|---|---|---|---|---|---|---|---|
| 0 | 1 | ABBEVILLE | SC | 2006 | 877 | 363620 | 45001 | 0.36362 |
| 1 | 2 | ABBEVILLE | SC | 2007 | 908 | 402940 | 45001 | 0.40294 |
| 2 | 3 | ABBEVILLE | SC | 2008 | 871 | 424590 | 45001 | 0.42459 |
| 3 | 4 | ABBEVILLE | SC | 2009 | 930 | 467230 | 45001 | 0.46723 |
| 4 | 5 | ABBEVILLE | SC | 2010 | 1197 | 539280 | 45001 | 0.53928 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 27753 | 27754 | NA | NV | 2007 | 447 | 200600 | NA | 0.20060 |
| 27754 | 27755 | NA | NV | 2008 | 5 | 2200 | NA | 0.00220 |
| 27755 | 27756 | NA | OH | 2006 | 23 | 5100 | NA | 0.00510 |
| 27756 | 27757 | NA | PR | 2006 | 10 | 17800 | NA | 0.01780 |
| 27757 | 27758 | NA | PR | 2007 | 2 | 1300 | NA | 0.00130 |
27758 rows × 8 columns
# Find avg pills in millions per year
avg_df = annual.groupby('year').mean()
avg_df.reset_index(inplace = True)
avg_df
| year | Pills_in_millions | |
|---|---|---|
| 0 | 2006 | 2.645410 |
| 1 | 2007 | 2.992099 |
| 2 | 2008 | 3.252162 |
| 3 | 2009 | 3.526038 |
| 4 | 2010 | 3.783656 |
| 5 | 2011 | 4.035583 |
| 6 | 2012 | 3.993064 |
| 7 | 2013 | 3.861752 |
| 8 | 2014 | 3.768738 |
fig = px.line(avg_df, x = 'year', y = 'Pills_in_millions', markers = True)
fig.update_layout(
title="Average Annual Opioid Pills Shipped Per County",
xaxis_title="Year",
yaxis_title="Annual Pills Shipped Per County (millions)",
legend_title="Legend Title"
)
fig.show()